SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
 CREATE FUNCTION	[dbo].[RollupInstrument](@FundID VARCHAR(MAX), @NAVDate DATETIME)
RETURNS TABLE 
AS 
RETURN
(
 SELECT     

 MAX(i.InstrumentId) instrID, MAX(@FundID) FundID, i.InstrName, SUM(a.EndAdjAmt) par

 
 FROM       FSFundModel_VALCOMM.Reporting.Asset a
            INNER JOIN FSFundModel_VALCOMM.Reporting.Instrument i
            ON         a.ExportId     = i.ExportId
            AND        a.InstrumentId = i.InstrumentId
            
            --LEFT JOIN FSFundModel_VALCOMM.Reporting.FundCreditInstrument fci
            --ON         i.ExportId               = fci.ExportId
            --AND        i.InstrumentId           = fci.InstrumentId
            --AND        a.FundCreditInstrumentId = fci.FundCreditInstrumentId
            
            --INNER JOIN FSFundModel_VALCOMM.Reporting.Issuer iss
            --ON         iss.ExportId = a.ExportId
            --AND        i.IssuerId   = iss.IssuerId
            
            INNER JOIN FSFundModel_VALCOMM.Reporting.InstrumentDefinition id
            ON         i.InstDefId = id.InstDefId
            AND        id.ExportId = a.ExportId
            
            --INNER JOIN FSFundModel_VALCOMM.Reporting.FundIssuer fi
            --ON         a.ExportId  = fi.ExportId
            --AND        fi.IssuerId = iss.IssuerId
            
            --INNER JOIN FSFundModel_VALCOMM.Reporting.Portfolio p
            --ON         a.ExportId    = p.ExportId
            --AND        a.PortfolioId = p.PortfolioId
            
            --INNER JOIN FSFundModel_VALCOMM.Reporting.Fund f
            --ON         a.ExportId = f.ExportId
            
            --INNER JOIN FSFundModel_VALCOMM.Reporting.Trade t
            --ON         a.ExportId     = t.ExportId
            --AND        i.InstrumentId = t.InstrumentId
            --AND        a.LotId        = t.LotId
            --AND        a.PortfolioId  = t.PortfolioId
            --AND        a.InstrumentId = t.InstrumentId
 WHERE      a.ExportId                =
            (SELECT MAX(e.ExportId)
            FROM    FSFundModel_VALCOMM.Reporting.Export e
            WHERE   e.FundId = @FundID
			AND e.NAVDate = @NAVDate )	
AND a.IsActiveEndOfDay= 1
AND id.HasShares <> 1
GROUP BY i.InstrName)
GO
